In [1]:
# Author: Stephen Situ
# In This exercise, I connect to an IBM DB2 cloud database and use SQL alchemy to preform advanced sql queries. This includes:
# Aggragate Functions, LIMIT, DISTINCT, LIKE, ORDER BY, GROUP BY, GROUPING SETS, HAVING, PARTITION BY, CASE, CREATE TABLE,
# ALTER TABLE, INSERT INTO, UPDATE, DROP TABLE, Subqueries, VIEW, WITH CTE, TEMP TABLE, STORED PROCEDURES, JOINS, UNION.
In [ ]:
import ibm_db
import ibm_db_sa
import sqlalchemy
%load_ext sql
In [2]:
%sql ibm_db_sa://gmv60736:vBlsVJ3UzuclM6c1@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
In [5]:
# Return full Census table
In [3]:
%%sql
SELECT * 
FROM CENSUS
LIMIT 10

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[3]:
community_area_number community_area_name percent_of_housing_crowded percent_households_below_poverty percent_aged_16__unemployed percent_aged_25__without_high_school_diploma percent_aged_under_18_or_over_64 per_capita_income hardship_index
1 Rogers Park 7.7 23.6 8.7 18.2 27.5 23939 39
2 West Ridge 7.8 17.2 8.8 20.8 38.5 23040 46
3 Uptown 3.8 24.0 8.9 11.8 22.2 35787 20
4 Lincoln Square 3.4 10.9 8.2 13.4 25.5 37524 17
5 North Center 0.3 7.5 5.2 4.5 26.2 57123 6
6 Lake View 1.1 11.4 4.7 2.6 17.0 60058 5
7 Lincoln Park 0.8 12.3 5.1 3.6 21.5 71551 2
8 Near North Side 1.9 12.9 7.0 2.5 22.6 88669 1
9 Edison Park 1.1 3.3 6.5 7.4 35.3 40959 8
10 Norwood Park 2.0 5.4 9.0 11.5 39.5 32875 21
In [7]:
# AGGRAGATE FUNCTIONS - AVG, COUNT, MAX, MIN return single value based on column values
In [4]:
%%sql
SELECT AVG(per_capita_income) AS avg_income
FROM CENSUS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[4]:
avg_income
25597
In [9]:
# LIMIT- limits result output
In [5]:
%%sql
SELECT * 
FROM CENSUS
LIMIT 3

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[5]:
community_area_number community_area_name percent_of_housing_crowded percent_households_below_poverty percent_aged_16__unemployed percent_aged_25__without_high_school_diploma percent_aged_under_18_or_over_64 per_capita_income hardship_index
1 Rogers Park 7.7 23.6 8.7 18.2 27.5 23939 39
2 West Ridge 7.8 17.2 8.8 20.8 38.5 23040 46
3 Uptown 3.8 24.0 8.9 11.8 22.2 35787 20
In [11]:
# DISTINCT - returns only unique values of a column
In [6]:
%%sql
SELECT DISTINCT community_area_name
FROM CENSUS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[6]:
community_area_name
Albany Park
Archer Heights
Armour Square
Ashburn
Auburn Gresham
Austin
Avalon Park
Avondale
Belmont Cragin
Beverly
Bridgeport
Brighton Park
Burnside
CHICAGO
Calumet Heights
Chatham
Chicago Lawn
Clearing
Douglas
Dunning
East Garfield Park
East Side
Edgewater
Edison Park
Englewood
Forest Glen
Fuller Park
Gage Park
Garfield Ridge
Grand Boulevard
Greater Grand Crossing
Hegewisch
Hermosa
Humboldt park
Hyde Park
Irving Park
Jefferson Park
Kenwood
Lake View
Lincoln Park
Lincoln Square
Logan Square
Loop
Lower West Side
McKinley Park
Montclaire
Morgan Park
Mount Greenwood
Near North Side
Near South Side
Near West Side
New City
North Center
North Lawndale
North Park
Norwood Park
O'Hare
Oakland
Portage Park
Pullman
Riverdale
Rogers Park
Roseland
South Chicago
South Deering
South Lawndale
South Shore
Uptown
Washington Height
Washington Park
West Elsdon
West Englewood
West Garfield Park
West Lawn
West Pullman
West Ridge
West Town
Woodlawn
In [13]:
# Count Distinct values for column
In [7]:
%%sql
SELECT COUNT(DISTINCT community_area_name)
FROM CENSUS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[7]:
1
78
In [15]:
# Use LiKE command to filter strings
In [8]:
%%sql
SELECT community_area_name
FROM CENSUS
WHERE community_area_name LIKE 'Burn%';

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[8]:
community_area_name
Burnside
In [17]:
# Use ORDER BY clause to sort
In [9]:
%%sql
SELECT per_capita_income
FROM CENSUS
ORDER BY per_capita_income DESC
LIMIT 5

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[9]:
per_capita_income
88669
71551
65526
60058
59077
In [19]:
# Use GROUP BY clause to group a column with aggragate functions
In [10]:
%%sql
SELECT community_area_name, AVG(per_capita_income) AS avg_income
FROM CENSUS
GROUP BY community_area_name
LIMIT 10

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[10]:
community_area_name avg_income
Albany Park 21323
Archer Heights 16134
Armour Square 16148
Ashburn 23482
Auburn Gresham 15528
Austin 15957
Avalon Park 24454
Avondale 20039
Belmont Cragin 15461
Beverly 39523
In [ ]:
# GROUPING SETS, can preform multiple group bys without a UNION
In [116]:
%%sql 
SELECT community_area_name, community_area_number, AVG(per_capita_income) AS average_income
FROM CENSUS
GROUP BY
    GROUPING SETS (
    community_area_name,community_area_number);

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[116]:
community_area_name community_area_number average_income
None 1 23939
None 2 23040
None 3 35787
None 4 37524
None 5 57123
None 6 60058
None 7 71551
None 8 88669
None 9 40959
None 10 32875
None 11 27751
None 12 44164
None 13 26576
None 14 21323
None 15 24336
None 16 27249
None 17 26282
None 18 22014
None 19 15461
None 20 15089
None 21 20039
None 22 31908
None 23 13781
None 24 43198
None 25 15957
None 26 10934
None 27 12961
None 28 44689
None 29 12034
None 30 10402
None 31 16444
None 32 65526
None 33 59077
None 34 16148
None 35 23791
None 36 19252
None 37 10432
None 38 23472
None 39 35911
None 40 13785
None 41 39056
None 42 18672
None 43 19398
None 44 18881
None 45 24454
None 46 16579
None 47 12515
None 48 28887
None 49 17949
None 50 20588
None 51 14685
None 52 17104
None 53 16563
None 54 8201
None 55 22677
None 56 26353
None 57 16134
None 58 13089
None 59 16954
None 60 22694
None 61 12765
None 62 15754
None 63 12171
None 64 25113
None 65 16907
None 66 13231
None 67 11317
None 68 11888
None 69 17285
None 70 23482
None 71 15528
None 72 39523
None 73 19713
None 74 34381
None 75 27149
None 76 25828
None 77 33385
None None 28202
Albany Park None 21323
Archer Heights None 16134
Armour Square None 16148
Ashburn None 23482
Auburn Gresham None 15528
Austin None 15957
Avalon Park None 24454
Avondale None 20039
Belmont Cragin None 15461
Beverly None 39523
Bridgeport None 22694
Brighton Park None 13089
Burnside None 12515
CHICAGO None 28202
Calumet Heights None 28887
Chatham None 18881
Chicago Lawn None 13231
Clearing None 25113
Douglas None 23791
Dunning None 26282
East Garfield Park None 12961
East Side None 17104
Edgewater None 33385
Edison Park None 40959
Englewood None 11888
Forest Glen None 44164
Fuller Park None 10432
Gage Park None 12171
Garfield Ridge None 26353
Grand Boulevard None 23472
Greater Grand Crossing None 17285
Hegewisch None 22677
Hermosa None 15089
Humboldt park None 13781
Hyde Park None 39056
Irving Park None 27249
Jefferson Park None 27751
Kenwood None 35911
Lake View None 60058
Lincoln Park None 71551
Lincoln Square None 37524
Logan Square None 31908
Loop None 65526
Lower West Side None 16444
McKinley Park None 16954
Montclaire None 22014
Morgan Park None 27149
Mount Greenwood None 34381
Near North Side None 88669
Near South Side None 59077
Near West Side None 44689
New City None 12765
North Center None 57123
North Lawndale None 12034
North Park None 26576
Norwood Park None 32875
O'Hare None 25828
Oakland None 19252
Portage Park None 24336
Pullman None 20588
Riverdale None 8201
Rogers Park None 23939
Roseland None 17949
South Chicago None 16579
South Deering None 14685
South Lawndale None 10402
South Shore None 19398
Uptown None 35787
Washington Height None 19713
Washington Park None 13785
West Elsdon None 15754
West Englewood None 11317
West Garfield Park None 10934
West Lawn None 16907
West Pullman None 16563
West Ridge None 23040
West Town None 43198
Woodlawn None 18672
In [21]:
# Add HAVING clause to filter group by statements
In [11]:
%%sql
SELECT community_area_name, AVG(per_capita_income) AS avg_income
FROM CENSUS
GROUP BY community_area_name
HAVING AVG(per_capita_income) > 20000
LIMIT 10

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[11]:
community_area_name avg_income
Albany Park 21323
Ashburn 23482
Avalon Park 24454
Avondale 20039
Beverly 39523
Bridgeport 22694
CHICAGO 28202
Calumet Heights 28887
Clearing 25113
Douglas 23791
In [ ]:
# PARTITION BY can let us aggregate without using GROUP BY statement
In [39]:
%%sql
SELECT  community_area_name, AVG(per_capita_income) OVER (PARTITION BY per_capita_income) AS average_income
FROM CENSUS
LIMIT 10

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[39]:
community_area_name average_income
Riverdale 8201
South Lawndale 10402
Fuller Park 10432
West Garfield Park 10934
West Englewood 11317
Englewood 11888
North Lawndale 12034
Gage Park 12171
Burnside 12515
New City 12765
In [ ]:
# Using CASE statement can create new column to categorize
In [47]:
%%sql 
SELECT community_area_name, per_capita_income,
CASE
    WHEN per_capita_income > 30000 and per_capita_income < 50000 THEN 'Mid Income'
    WHEN per_capita_income > 50000 THEN 'High Income'
    ELSE 'Low Income'
END AS income_bracket
FROM CENSUS
LIMIT 10;

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[47]:
community_area_name per_capita_income income_bracket
Rogers Park 23939 Low Income
West Ridge 23040 Low Income
Uptown 35787 Mid Income
Lincoln Square 37524 Mid Income
North Center 57123 High Income
Lake View 60058 High Income
Lincoln Park 71551 High Income
Near North Side 88669 High Income
Edison Park 40959 Mid Income
Norwood Park 32875 Mid Income
In [23]:
# Create Table CATS with defined schema
In [13]:
%%sql   
CREATE TABLE CATS(
   ID   INT              NOT NULL,
   COLOUR VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,       
    PRIMARY KEY (ID)
);

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[13]:
[]
In [14]:
%%sql 
SELECT *
FROM CATS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[14]:
id colour age
In [15]:
%%sql
INSERT INTO CATS(id,colour,age)
VALUES (12,'RED',10),
(13,'BLUE',14),
(24,'ORANGE',57),
(50,'YELLOW',48);

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
4 rows affected.
Out[15]:
[]
In [16]:
%%sql 
SELECT *
FROM CATS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[16]:
id colour age
12 RED 10
13 BLUE 14
24 ORANGE 57
50 YELLOW 48
In [17]:
%%sql
UPDATE CATS
SET colour='CYAN'
WHERE age=10;

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
1 rows affected.
Out[17]:
[]
In [18]:
%%sql 
SELECT *
FROM CATS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[18]:
id colour age
12 CYAN 10
13 BLUE 14
24 ORANGE 57
50 YELLOW 48
In [30]:
# ALTER TABLE Commands - ADD COLUMN, DROP COLUMN, RENAME COLUMN, ALTER COLUMN
In [31]:
# ALTER TABLE - ADD COLUMN
In [19]:
%%sql 
ALTER TABLE CATS
ADD COLUMN Year INT;

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[19]:
[]
In [20]:
%%sql 
SELECT *
FROM CATS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[20]:
id colour age YEAR
12 CYAN 10 None
13 BLUE 14 None
24 ORANGE 57 None
50 YELLOW 48 None
In [34]:
# ALTER TABLE - RENAME COLUMN
In [21]:
%%sql
ALTER TABLE CATS
RENAME COLUMN YEAR TO 
year_born;

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[21]:
[]
In [22]:
%%sql 
SELECT *
FROM CATS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[22]:
id colour age year_born
12 CYAN 10 None
13 BLUE 14 None
24 ORANGE 57 None
50 YELLOW 48 None
In [37]:
#ALTER TABLE - ALTER COLUMN
In [23]:
%%sql
ALTER TABLE CATS 
ALTER COLUMN year_born 
SET DATA TYPE varchar(10);

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[23]:
[]
In [39]:
#ALTER TABLE - DROP COLUMN
In [24]:
%%sql
ALTER TABLE CATS
DROP COLUMN year_born

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[24]:
[]
In [25]:
%%sql 
SELECT *
FROM CATS

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[25]:
id colour age
12 CYAN 10
13 BLUE 14
24 ORANGE 57
50 YELLOW 48
In [59]:
# DROP TABLE can delete the cats table
In [58]:
%%sql
DROP TABLE CATS;

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[58]:
[]
In [44]:
 # SUBQUERIES - subquery in FROM clause gives resultant table to query off of
In [62]:
%%sql
SELECT * 
FROM (
SELECT * 
FROM CENSUS
WHERE per_capita_income >= 50000)
LIMIT 10

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[62]:
community_area_number community_area_name percent_of_housing_crowded percent_households_below_poverty percent_aged_16__unemployed percent_aged_25__without_high_school_diploma percent_aged_under_18_or_over_64 per_capita_income hardship_index
5 North Center 0.3 7.5 5.2 4.5 26.2 57123 6
6 Lake View 1.1 11.4 4.7 2.6 17.0 60058 5
7 Lincoln Park 0.8 12.3 5.1 3.6 21.5 71551 2
8 Near North Side 1.9 12.9 7.0 2.5 22.6 88669 1
32 Loop 1.5 14.7 5.7 3.1 13.5 65526 3
33 Near South Side 1.3 13.8 4.9 7.4 21.8 59077 7
In [45]:
# SUBQUERIES - subquery in where clause gives value to compare to off a aggregate function
In [64]:
%%sql 
SELECT *
FROM CENSUS
WHERE per_capita_income > (
SELECT avg(per_capita_income)
FROM CENSUS
)
LIMIT 10

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[64]:
community_area_number community_area_name percent_of_housing_crowded percent_households_below_poverty percent_aged_16__unemployed percent_aged_25__without_high_school_diploma percent_aged_under_18_or_over_64 per_capita_income hardship_index
3 Uptown 3.8 24.0 8.9 11.8 22.2 35787 20
4 Lincoln Square 3.4 10.9 8.2 13.4 25.5 37524 17
5 North Center 0.3 7.5 5.2 4.5 26.2 57123 6
6 Lake View 1.1 11.4 4.7 2.6 17.0 60058 5
7 Lincoln Park 0.8 12.3 5.1 3.6 21.5 71551 2
8 Near North Side 1.9 12.9 7.0 2.5 22.6 88669 1
9 Edison Park 1.1 3.3 6.5 7.4 35.3 40959 8
10 Norwood Park 2.0 5.4 9.0 11.5 39.5 32875 21
11 Jefferson Park 2.7 8.6 12.4 13.4 35.5 27751 25
12 Forest Glen 1.1 7.5 6.8 4.9 40.5 44164 11
In [46]:
# VIEWS can create a tempoary view that can be queried from
In [ ]:
%%sql
CREATE VIEW area_uptown AS
SELECT community_area_name, per_capita_income
FROM CENSUS
WHERE community_area_name = 'Uptown';
In [121]:
%%sql
select * FROM area_uptown

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[121]:
community_area_name per_capita_income
Uptown 35787
In [79]:
# COMMON TABLE EXPRESSION create a temporary table and a SELECT STATEMENT must be followed immediately after to query off of
In [78]:
%%sql
WITH CENSUS_CTE (area_name, Income) AS (   
SELECT community_area_name, per_capita_income
FROM CENSUS
WHERE community_area_name = 'North Center')                               
select area_name, Income
FROM CENSUS_CTE

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[78]:
area_name income
North Center 57123
In [ ]:
# TEMP TABLES create a temporary table to query off of. Can Use INSERT INTO statement to put values inside temp table
In [83]:
%%sql
CREATE TABLE #temp_census (
ID int,
Jobtitle varchar (50),
Salary int
)

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[83]:
[]
In [96]:
%%sql 
SELECT *
FROM #temp_census

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
Out[96]:
id jobtitle salary
In [47]:
# STORED PROCEDURES are like functions that can be called
In [108]:
%%sql
CREATE PROCEDURE RETRIEVE_ALL       
LANGUAGE SQL                        
READS SQL DATA                      
DYNAMIC RESULT SETS 1              
BEGIN 
DECLARE C1 CURSOR               
WITH RETURN FOR                 
SELECT * FROM CENSUS;         
OPEN C1;                        
END

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0454N  The signature provided in the definition for routine "GMV60736.RETRIEVE_ALL" matches the signature of some other routine.  LINE NUMBER=10.  SQLSTATE=42723\r SQLCODE=-454
[SQL: CREATE PROCEDURE RETRIEVE_ALL       
LANGUAGE SQL                        
READS SQL DATA                      
DYNAMIC RESULT SETS 1              
BEGIN 
DECLARE C1 CURSOR               
WITH RETURN FOR                 
SELECT * FROM CENSUS;         
OPEN C1;                        
END]
(Background on this error at: http://sqlalche.me/e/f405)
In [114]:
%%sql 
CALL RETRIEVE_ALL

 * ibm_db_sa://gmv60736:***@m98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud:30875/bludb;security=SSL
Done.
(ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: The last call to execute did not produce any result set.
(Background on this error at: http://sqlalche.me/e/f405)
In [ ]:
# JOINS - 4 Major Joins. Uses Primary Key from one table and a foreign key from another table to join. 
# (INNER) JOIN
# LEFT (OUTER) JOIN
# RIGHT (OUTER) JOIN
# FULL (OUTER) JOIN
In [ ]:
# UNIONS - adds rows from one table to another using UNION (distinct) or UNION ALL statement (all)